create database OADatabase
go
use OADatabase
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CompanyInfo') and o.name = 'FK_COMPANYI_REFERENCE_DEPARTME')
alter table CompanyInfo
   drop constraint FK_COMPANYI_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_EMPLOYEE')
alter table DepartmentInfo
   drop constraint FK_DEPARTME_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_POSITION')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_POSITION
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserApplyInfo') and o.name = 'FK_USERAPPL_REFERENCE_PLANNING')
alter table UserApplyInfo
   drop constraint FK_USERAPPL_REFERENCE_PLANNING
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserInfo') and o.name = 'FK_USERINFO_REFERENCE_USERAPPL')
alter table UserInfo
   drop constraint FK_USERINFO_REFERENCE_USERAPPL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CompanyInfo')
            and   type = 'U')
   drop table CompanyInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeInfo')
            and   type = 'U')
   drop table EmployeeInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PlanningTasks')
            and   type = 'U')
   drop table PlanningTasks
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PositionInfo')
            and   type = 'U')
   drop table PositionInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplyInfo')
            and   type = 'U')
   drop table UserApplyInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: CompanyInfo                                           */
/*==============================================================*/
create table CompanyInfo (
   DepartmentId         int                  not null,
   PlanningDepartment   nvarchar(80)         null,
   SalesDepartment      nvarchar(80)         null,
   constraint PK_COMPANYINFO primary key (DepartmentId)
)
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   DepartmentId         int                  identity,
   EmployeeId           int                  null,
   DepartmengtName      nvarchar(80)         null,
   DepartmentSupervisor nvarchar(80)         null,
   Telephone            nvarchar(80)         null,
   Email                nvarchar(80)         null,
   constraint PK_DEPARTMENTINFO primary key (DepartmentId)
)
go

/*==============================================================*/
/* Table: EmployeeInfo                                          */
/*==============================================================*/
create table EmployeeInfo (
   EmployeeId           int                  identity,
   PositionId           int                  null,
   Name                 nvarchar(80)         null,
   Position             nvarchar(80)         null,
   Telephone            nvarchar(80)         null,
   Pay                  nvarchar(80)         null,
   constraint PK_EMPLOYEEINFO primary key (EmployeeId)
)
go

/*==============================================================*/
/* Table: PlanningTasks                                         */
/*==============================================================*/
create table PlanningTasks (
   ApplyId              int                  identity,
   Tasks                nvarchar(80)         null,
   Datetime             datetime             null,
   constraint PK_PLANNINGTASKS primary key (ApplyId)
)
go

/*==============================================================*/
/* Table: PositionInfo                                          */
/*==============================================================*/
create table PositionInfo (
   PositionId           int                  identity,
   Supervisor           nvarchar(80)         null,
   Salesman             nvarchar(80)         null,
   Schemer              nvarchar(80)         null,
   constraint PK_POSITIONINFO primary key (PositionId)
)
go

/*==============================================================*/
/* Table: UserApplyInfo                                         */
/*==============================================================*/
create table UserApplyInfo (
   ApplyId              int                  not null,
   UserId               int                  null,
   Datetime             datetime             null,
   Days                 nvarchar(80)         null,
   People               nvarchar(80)         null,
   Address              nvarchar(80)         null,
   Budget               nvarchar(80)         null,
   Acceptance           nvarchar(80)         null,
   constraint PK_USERAPPLYINFO primary key (ApplyId)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               int                  identity,
   ApplyId              int                  null,
   UserName             nvarchar(80)         null,
   BankAccount          nvarchar(80)         null,
   Telephone            nvarchar(80)         null,
   Address              nvarchar(80)         null,
   constraint PK_USERINFO primary key (UserId)
)
go

alter table CompanyInfo
   add constraint FK_COMPANYI_REFERENCE_DEPARTME foreign key (DepartmentId)
      references DepartmentInfo (DepartmentId)
go

alter table DepartmentInfo
   add constraint FK_DEPARTME_REFERENCE_EMPLOYEE foreign key (EmployeeId)
      references EmployeeInfo (EmployeeId)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_POSITION foreign key (PositionId)
      references PositionInfo (PositionId)
go

alter table UserApplyInfo
   add constraint FK_USERAPPL_REFERENCE_PLANNING foreign key (ApplyId)
      references PlanningTasks (ApplyId)
go

alter table UserInfo
   add constraint FK_USERINFO_REFERENCE_USERAPPL foreign key (ApplyId)
      references UserApplyInfo (ApplyId)
go

